﻿using BusinessRepository;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;

using learun.util;

using System.Data;
using System.Dynamic;
using System.IO;


namespace BusinessRule
{
    public class ProBBRule
    {
        public static async Task<DataTable> GetEchartsXMHTZF(string strlx, string strxmnf, string strdepid)
        {
            string strsql = "";
            if (strlx == "xmlx")
            {
                strsql = "select xmflname name, COUNT(*)cc from CProManage where xmnf = '" + strxmnf + "' and departid in " + strdepid + " group by xmflname";
            }
            else if (strlx == "htlx")
            {
                strsql = "select htlx name,COUNT(*)cc from CProContract where DATEPART(YEAR, htrq) = '" + strxmnf + "' " +
                    @"and dxid in (select id from CProManageLX where xmid in (
select id from CProManage where departid in " + strdepid + ")) group by htlx";
            }
            else if (strlx == "zf")
            {
                strsql = @"with t as
(select number rn from master..spt_values where type = 'p' and number < 12)

select sj name, sqje cc, zfje  from(
  select substring(CONVERT(varchar(100), DATEADD(MONTH, 0 - rn, CONVERT(datetime, '"+ strxmnf + @"-12-01')), 23), 1, 7) sj  from t
  )tt
  left join(
   select SUM(sqje)sqje, substring(CONVERT(varchar(100), sqrq, 23), 1, 7) months from CProFKSQ
   where htid in (
 select id from CProContract where dxid in (
 select id from CProManageLX where xmid in (
 select id from CProManage where departid in " + strdepid + @"
 ) ) )
   group by substring(CONVERT(varchar(100), sqrq, 23), 1, 7)
  )tsq on  tt.sj = tsq.months
left join(
 select SUM(zfje) zfje, substring(CONVERT(varchar(100), zfrq, 23),1,7) months from CProFKMX
 where sqid in (
 select id from CProFKSQ where htid in(
 select id from CProContract where dxid in (
 select id from CProManageLX where xmid in (
 select id from CProManage where departid in " + strdepid + @"
 ) ) ) )
   group by substring(CONVERT(varchar(100), zfrq, 23),1,7)
)tmx on  tt.sj = tmx.months";
            }

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }


        public static async Task<dynamic> GetEcharts(string strxmnf, string strdepid)
        {
            int xmnf1 = CommonHelper.ParseInt(strxmnf) - 1;
            int xmnf = CommonHelper.ParseInt(strxmnf);

            strdepid = strdepid.Replace("'", "''");
            string strsql = "exec GetEcharts '" + xmnf1 + "','" + xmnf + "','" + strdepid + "'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }

        public static async Task<dynamic> GetEcharts2(string strxmnf, string strdepid)
        {
            int xmnf = CommonHelper.ParseInt(strxmnf);
            strdepid = strdepid.Replace("'", "''");


            dynamic d = new ExpandoObject();
            string strsql = "exec GetEchartsCQ '" + xmnf + "','" + strdepid + "','1'";
            var htgd = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "exec GetEchartsCQ '" + xmnf + "','" + strdepid + "','2'";
            var yjhcf = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "exec GetEchartsCQ '" + xmnf + "','" + strdepid + "','3'";
            var yfcf = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "exec GetEchartsCQ '" + xmnf + "','" + strdepid + "','4'";
            var htcf = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "exec GetEchartsCQ '" + xmnf + "','" + strdepid + "','5'";
            var zbdw = await new RepositoryFactory().BaseRepository().FindEntity(strsql);
   

            d.htgd = htgd;
            d.yjhcf = yjhcf;
            d.yfcf = yfcf;
            d.htcf = htcf;
            d.zbdw = zbdw;

            return d;
        }

        public static async Task<(IEnumerable<dynamic>, int)> GetHTBBList(dynamic dye, string strdepids)
        {
            string strxmmc = dye.xmmc;
            string strhtmc = dye.htmc;
            string strkssj = dye.kssj;
            string strjssj = dye.jssj;

            string strhtid = dye.htid;
            string strhtbh = dye.htbh;

            int current = dye.current;
            int size = dye.size;
            string sort = dye.sort ?? "";
            string column = dye.column ?? "";

            string str = "";
            if (!(string.IsNullOrEmpty(strxmmc)))
            {
                str = str + " and xmmc = '" + strxmmc + "'";
            }

            if (!(string.IsNullOrEmpty(strhtmc)))
            {
                str = str + " and htmc like '%" + strhtmc + "%'";
            }

            if (!(string.IsNullOrEmpty(strkssj)))
            {
                str += " and kgrq >= '" + strkssj + "'";
            }
            if (!(string.IsNullOrEmpty(strjssj)))
            {
                str += " and jgrq >= '" + strjssj + "'";
            }

            if (!(string.IsNullOrEmpty(strhtbh)))
            {
                str = str + " and htbh = '" + strhtbh + "'";
            }
            if (!(string.IsNullOrEmpty(strhtid)))
            {
                str = str + " and id = '" + strhtid + "'";
            }

            //await UserRule.GetUserDepid(uid)
            string sql = @"select htbh,htmc,dxmc,htje,bgje,sdje,(
select yingfu from View_FKJHTOYFJE where id = View_ProFKLists.id
)yingfu,zfje,wfje,zfbl from View_ProFKLists where departid in " + strdepids + str.Replace("''", "'");

            bool isAsc = sort.ToLower() == "asc";

            if (string.IsNullOrEmpty(column))
            {
                column = "htbh";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, column, isAsc, size.ToInt(), current.ToInt());

        }

//        public static async Task<(IEnumerable<dynamic>, int)> GetHTCFBBList(dynamic dye, string strdepids)
//        {
//            string strxmmc = dye.xmmc;
//            string strhtmc = dye.htmc;
//            string strcfje = dye.cfje;



//            int current = dye.current;
//            int size = dye.size;
//            string sort = dye.sort ?? "";
//            string column = dye.column ?? "";

//            string str = "";
//            if (!(string.IsNullOrEmpty(strxmmc)))
//            {
//                str = str + " and xmmc = '" + strxmmc + "'";
//            }

//            if (!(string.IsNullOrEmpty(strhtmc)))
//            {
//                str = str + " and htmc like '%" + strhtmc + "%'";
//            }

//            if (!(string.IsNullOrEmpty(strcfje)))
//            {
//                str += " and gck - zfje > " + strcfje;
//            }


//            //await UserRule.GetUserDepid(uid)
//            string sql = @"select htbh,htmc,dxmc,htje,bgje,sdje,(
//select yingfu from View_FKJHTOYFJE where id = View_ProFKLists.id
//)yingfu,zfje,wfje,zfbl from View_ProFKLists where departid in " + strdepids + str.Replace("''", "'");

//            bool isAsc = sort.ToLower() == "asc";

//            if (string.IsNullOrEmpty(column))
//            {
//                column = "htbh";
//            }
//            return await new RepositoryFactory().BaseRepository().FindList(sql, null, column, isAsc, size.ToInt(), current.ToInt());

//        }

        //合同和付款计划超付列表
        public static async Task<(IEnumerable<dynamic>, int)> GetHTFKJHCFBBList(dynamic dye, string strdepids, string strlx)
        {
            string strxmmc = dye.xmmc;
            string strhtmc = dye.htmc;
            string strcfje = dye.cfje;



            int current = dye.current;
            int size = dye.size;
            string sort = dye.sort ?? "";
            string column = dye.column ?? "";

            string str = "";
            if (!(string.IsNullOrEmpty(strxmmc)))
            {
                str = str + " and xmmc = '" + strxmmc + "'";
            }

            if (!(string.IsNullOrEmpty(strhtmc)))
            {
                str = str + " and htmc like '%" + strhtmc + "%'";
            }

            string strwhere = "";

            if (strlx == "fkjhcf")
            {
                if (!(string.IsNullOrEmpty(strcfje)))
                {
                    strwhere += " and yingfu - zfje > " + strcfje;
                }
            }
            else if (strlx == "htcf")
            {
                if (!(string.IsNullOrEmpty(strcfje)))
                {
                    strwhere += " and gck - zfje > " + strcfje;
                }
            }


            //await UserRule.GetUserDepid(uid)
            string sql = @"
select *,yingfu - zfje cfje,gck - zfje cfje2 from (
select htbh,htmc,dxmc,htje,bgje,sdje,gck,(
select yingfu from View_FKJHTOYFJE where id = View_ProFKLists.id
)yingfu,zfje,wfje,zfbl from View_ProFKLists  where departid in " + strdepids + str.Replace("''", "'") + @"
)t where 1=1 " + strwhere;

            bool isAsc = sort.ToLower() == "asc";

            if (string.IsNullOrEmpty(column))
            {
                column = "htbh";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, column, isAsc, size.ToInt(), current.ToInt());

        }
    }
}
